﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class IncrementGateway:DBGateway
    {
        public int GetIncrementNumber(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select IncrementNumber from tblIncrement where EmpId='" + empId + "'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                int number = 0;
                while (reader.Read())
                {
                    number = Convert.ToInt32(reader[0]);
                }
                return number;
            }
            catch (Exception)
            {
                throw new Exception("Error in DB while reading info");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public string SaveIncrement(int empId, Increment increment)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblIncrement values(@EmpId,@Basic,@Medical,@Residence,@Transport,@PF,@Mobile,@Others,@Gross,@date,@num,@TotalIncrement)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@Basic", SqlDbType.Decimal).Value = increment.Basic;
                CommandObj.Parameters.Add("@Medical", SqlDbType.Decimal).Value = increment.Medical;
                CommandObj.Parameters.Add("@Residence", SqlDbType.Decimal).Value = increment.Residence;
                CommandObj.Parameters.Add("@Transport", SqlDbType.Decimal).Value = increment.Transport;
                CommandObj.Parameters.Add("@PF", SqlDbType.Decimal).Value = increment.PF;
                CommandObj.Parameters.Add("@Mobile", SqlDbType.Decimal).Value = increment.Mobile;
                CommandObj.Parameters.Add("@Others", SqlDbType.Decimal).Value = increment.Others;
                CommandObj.Parameters.Add("@Gross", SqlDbType.Decimal).Value = increment.Gross;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = increment.Date;
                CommandObj.Parameters.Add("@num",SqlDbType.Int).Value = increment.IncrementNumber;
                CommandObj.Parameters.Add("@TotalIncrement",SqlDbType.Int).Value = increment.TotalIncrement;
                CommandObj.ExecuteNonQuery();
                return "Salary Structure Incremented Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Salary Structure Incrementing");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public string EditIncrement(int empId, Increment increment)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblIncrement set EmpId=@EmpId,Basic=@Basic,Medical=@Medical,Residence=@Residence,Transport=@Transport," +
                                                       "PF=@PF,Mobile@Mobile,Others=@Others,Gross=@Gross,date=@date,IncrementNumber=@num,TotalIncrement@TotalIncrement " +
                                                       "where EmpId='" + empId + "' and IncrementNumber='"+increment.IncrementNumber+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@Basic", SqlDbType.Decimal).Value = increment.Basic;
                CommandObj.Parameters.Add("@Medical", SqlDbType.Decimal).Value = increment.Medical;
                CommandObj.Parameters.Add("@Residence", SqlDbType.Decimal).Value = increment.Residence;
                CommandObj.Parameters.Add("@Transport", SqlDbType.Decimal).Value = increment.Transport;
                CommandObj.Parameters.Add("@PF", SqlDbType.Decimal).Value = increment.PF;
                CommandObj.Parameters.Add("@Mobile", SqlDbType.Decimal).Value = increment.Mobile;
                CommandObj.Parameters.Add("@Others", SqlDbType.Decimal).Value = increment.Others;
                CommandObj.Parameters.Add("@Gross", SqlDbType.Decimal).Value = increment.Gross;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = increment.Date;
                CommandObj.Parameters.Add("@num", SqlDbType.Int).Value = increment.IncrementNumber;
                CommandObj.Parameters.Add("@TotalIncrement", SqlDbType.Int).Value = increment.TotalIncrement;
                CommandObj.ExecuteNonQuery();
                return "Salary Increment Edited Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Editing Salary Increment");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Increment> GetIncrementInfo(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblIncrement where EmpId='"+empId+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<Increment>increments=new List<Increment>();
                while (reader.Read())
                {
                    Increment increment=new Increment();
                    Employee employee = new Employee();
                    increment.Id = Convert.ToInt32(reader[0]);
                    employee.Id = Convert.ToInt32(reader[1]);
                    increment.Employee = employee;
                    increment.Basic = Convert.ToDecimal(reader[2]);
                    increment.Medical = Convert.ToDecimal(reader[3]);
                    increment.Residence = Convert.ToDecimal(reader[4]);
                    increment.Transport = Convert.ToDecimal(reader[5]);
                    increment.PF = Convert.ToDecimal(reader[6]);
                    increment.Mobile = Convert.ToDecimal(reader[7]);
                    increment.Others = Convert.ToDecimal(reader[8]);
                    increment.Gross = Convert.ToDecimal(reader[9]);
                    increment.Date = Convert.ToDateTime(reader[10]);
                    increment.IncrementNumber = Convert.ToInt32(reader[11]);
                    increment.TotalIncrement = Convert.ToInt32(reader[12]);
                    increments.Add(increment);
                }
                return increments;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
             ConnectionObj.Close();   
            }
        }
    }
}